﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace VK.Comm.TransactSql
{
    public class ConditionProcess
    {
        /// <summary>
        /// 给SQL命令添加Where条件，给Command添加参数。
        /// </summary>
        /// <param name="db">Database</param>
        /// <param name="conditionArray">单字段搜索条件</param>
        /// <param name="sqlText">返回SQL命令</param>
        /// <param name="parameterlst">返回参数列表</param>
        public static void DisposeCommandByCondition(Database db, IList<Condition> conditionArray, ref string sqlText, ref List<DbParameter> parameterlst)
        {
            DisposeCommandByCondition(db, conditionArray, null, ref sqlText, ref parameterlst);
        }

        /// <summary>
        /// 给SQL命令添加Where条件，给Command添加参数。（支持两字段区间搜索）
        /// </summary>
        /// <param name="db">Database</param>
        /// <param name="conditionArray">单字段搜索条件</param>
        /// <param name="distConditionArray">两字段区间搜索条件</param>
        /// <param name="sqlText">返回SQL命令</param>
        /// <param name="parameterlst">返回参数列表</param>
        public static void DisposeCommandByCondition(Database db, IList<Condition> conditionArray, IList<DistCondition> distConditionArray, ref string sqlText, ref List<DbParameter> parameterlst)
        {
            StringBuilder conditionstr = new StringBuilder();
            if (conditionArray != null)
            {   
                #region 更具类型加载参数
                foreach (Condition condition in conditionArray)
                {
                    if (condition.FieldName != null && condition.ConditionValue != null)
                    {
                        switch (condition.ConditionType)
                        {
                            case ConditionType.TextLIKE:
                                if (!string.IsNullOrEmpty(condition.ConditionValue.ToString()))
                                {
                                    conditionstr.Append(" AND (" + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] LIKE @" + condition.TableName + condition.ParameterName + ")");
                                    parameterlst.Add(DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName, condition.DbType, "%" + condition.ConditionValue + "%"));
                                }
                                break;
                            case ConditionType.TextEqual:
                                if (!string.IsNullOrEmpty(condition.ConditionValue.ToString()))
                                {
                                    conditionstr.Append(" AND (" + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] = @" + condition.TableName + condition.ParameterName + ")");
                                    parameterlst.Add(DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName, condition.DbType, condition.ConditionValue));
                                }
                                break;
                            case ConditionType.NumeralEqual:
                                if (int.Parse(condition.ConditionValue.ToString()) != 0)
                                {
                                    conditionstr.Append(" AND (" + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] = @" + condition.TableName + condition.ParameterName + ")");
                                    parameterlst.Add(DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName, condition.DbType, condition.ConditionValue));
                                }
                                break;
                            case ConditionType.NumeralNotEqual:
                                if (int.Parse(condition.ConditionValue.ToString()) != 0)
                                {
                                    conditionstr.Append(" AND (" + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] <> @" + condition.TableName + condition.ParameterName + ")");
                                    parameterlst.Add(DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName, condition.DbType, condition.ConditionValue));
                                }
                                break;
                            case ConditionType.NumeralMoreThanEqual:
                                if (int.Parse(condition.ConditionValue.ToString()) != 0)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] >= @" + condition.TableName + condition.ParameterName + "_MoreThanEqual");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_MoreThanEqual", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.NumeralLittleThanEqual:
                                if (int.Parse(condition.ConditionValue.ToString()) != 0)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] <= @" + condition.TableName + condition.ParameterName + "_LittleThanEqual");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_LittleThanEqual", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.NumeralMoreThan:
                                if (int.Parse(condition.ConditionValue.ToString()) != 0)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] > @" + condition.TableName + condition.ParameterName + "_MoreThan");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_MoreThan", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.NumeralLittleThan:
                                if (int.Parse(condition.ConditionValue.ToString()) != 0)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] < @" + condition.TableName + condition.ParameterName + "_LittleThan");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_LittleThan", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.DateTimeMoreThanEqual:
                                if (!string.IsNullOrEmpty(condition.ConditionValue.ToString()) && DateTime.Parse(condition.ConditionValue.ToString()) != DateTime.MinValue)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] >= @" + condition.TableName + condition.ParameterName + "_MoreThanEqual");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_MoreThanEqual", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.DateTimeLittleThanEqual:
                                if (!string.IsNullOrEmpty(condition.ConditionValue.ToString()) && DateTime.Parse(condition.ConditionValue.ToString()) != DateTime.MinValue)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] <= @" + condition.TableName + condition.ParameterName + "_LittleThanEqual");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_LittleThanEqual", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.DateTimeMoreThan:
                                if (!string.IsNullOrEmpty(condition.ConditionValue.ToString()) && DateTime.Parse(condition.ConditionValue.ToString()) != DateTime.MinValue)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] > @" + condition.TableName + condition.ParameterName + "_MoreThan");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_MoreThan", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.DateTimeLittleThan:
                                if (!string.IsNullOrEmpty(condition.ConditionValue.ToString()) && DateTime.Parse(condition.ConditionValue.ToString()) != DateTime.MinValue)
                                {
                                    conditionstr.Append(" AND " + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : (condition.TableName + ".")) + "[" + condition.FieldName + "] < @" + condition.TableName + condition.ParameterName + "_LittleThan");
                                    DbParameter parameter = DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName + "_LittleThan", condition.DbType, condition.ConditionValue);
                                    if (!parameterlst.Contains(parameter))
                                    {
                                        parameterlst.Add(parameter);
                                    }
                                }
                                break;
                            case ConditionType.BoolEqual:
                                conditionstr.Append(" AND (" + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] = @" + condition.TableName + condition.ParameterName + ")");
                                parameterlst.Add(DbHelper.PackInParameter(db, "@" + condition.TableName + condition.ParameterName, condition.DbType, condition.ConditionValue));
                                break;
                            case ConditionType.ISNULL:
                                conditionstr.Append(" AND (" + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] IS NULL)");
                                break;
                            case ConditionType.ISNOTNULL:
                                conditionstr.Append(" AND (" + (string.IsNullOrEmpty(condition.TableName) ? string.Empty : ("[" + condition.TableName + "].")) + "[" + condition.FieldName + "] IS NOT NULL)");
                                break;
                        }
                    }
                }
                #endregion
            }

            if (distConditionArray != null)
            {
                foreach (DistCondition dist in distConditionArray)
                {
                    if (dist.ConditionValueBegin != null && dist.ConditionValueEnd != null)
                    {
                        conditionstr.Append(" AND ( (").Append(dist.FieldNameBegin).Append(" <= @").Append(dist.FieldNameEnd + "DistEnd").Append(") AND (").Append(dist.FieldNameBegin).Append(" >= @").Append(dist.FieldNameBegin + "DistBegin)")
                            .Append(" OR (").Append(dist.FieldNameEnd).Append(" <= @").Append(dist.FieldNameEnd + "DistEnd").Append(") AND (").Append(dist.FieldNameEnd).Append(" >= @").Append(dist.FieldNameBegin + "DistBegin) ")
                            .Append(" OR (").Append(dist.FieldNameBegin).Append(" <= @").Append(dist.FieldNameBegin + "DistBegin").Append(") AND (").Append(dist.FieldNameEnd).Append(" >= @").Append(dist.FieldNameEnd + "DistEnd) )");
                        parameterlst.Add(DbHelper.PackInParameter(db, "@" + dist.FieldNameBegin + "DistBegin", dist.DbType, dist.ConditionValueBegin));
                        parameterlst.Add(DbHelper.PackInParameter(db, "@" + dist.FieldNameEnd + "DistEnd", dist.DbType, dist.ConditionValueEnd));
                    }
                    if (dist.ConditionValueBegin != null && dist.ConditionValueEnd == null)
                    {
                        conditionstr.Append(" AND ( (").Append(dist.FieldNameBegin).Append(" >= @").Append(dist.FieldNameBegin + "DistBegin").Append(") OR (").Append(dist.FieldNameEnd).Append(" >= @").Append(dist.FieldNameBegin + "DistBegin) )");
                        parameterlst.Add(DbHelper.PackInParameter(db, "@" + dist.FieldNameBegin + "DistBegin", dist.DbType, dist.ConditionValueBegin));
                    }
                    if (dist.ConditionValueBegin == null && dist.ConditionValueEnd != null)
                    {
                        conditionstr.Append(" AND ( (").Append(dist.FieldNameBegin).Append(" <= @").Append(dist.FieldNameEnd + "DistEnd").Append(") OR (").Append(dist.FieldNameEnd).Append(" <= @").Append(dist.FieldNameEnd + "DistEnd) )");
                        parameterlst.Add(DbHelper.PackInParameter(db, "@" + dist.FieldNameEnd + "DistEnd", dist.DbType, dist.ConditionValueEnd));
                    }
                }
            }
            string wherestr = conditionstr.ToString();
            if (!string.IsNullOrEmpty(wherestr))
            {
                if (!sqlText.Contains(" WHERE "))
                {//加where去and
                    wherestr = " WHERE " + wherestr.Remove(0, 5);
                }
                sqlText += wherestr;
            }
        }
    }
}